<?php

require("./mpcamera/setup.php");

$smarty = new Smarty_MPCamera();

$action = filter_input(INPUT_GET, "action");
$submit = strtolower(filter_input(INPUT_POST, "submit"));
$content = filter_input(INPUT_GET, "content");


if (1 == 1) {
    // Select PhuongThucThanhToan
    $query_phuongthucthanhtoan = "select * from `phuongthucthanhtoan`";
    $result_phuongthucthanhtoan = mysql_query($query_phuongthucthanhtoan);
    while ($row = mysql_fetch_array($result_phuongthucthanhtoan)) {
        $phuongthucthanhtoan[] = $row;
    }
    $smarty->assign("phuongthucthanhtoan", $phuongthucthanhtoan);

    // Select ALL DiaDiem
    $query_diadiem = "select `MaDD`, `TenDD` from `diadiem`";
    $result_diadiem = mysql_query($query_diadiem);
    while ($row = mysql_fetch_array($result_diadiem)) {
        $diadiem[] = $row;
    }
    $smarty->assign("diadiem", $diadiem);
}

// OK
if ($action == "add") {
    // Nothing
}

// OK 2015-02-05
if ($submit == "add") {
    $success = 0;

    $maDD = filter_input(INPUT_POST, "maDD");
    $maHD = addslashes(filter_input(INPUT_POST, "maHD"));
    $maPTTT = filter_input(INPUT_POST, "phuongthucthanhtoan");
    $thoigianbatdau = filter_input(INPUT_POST, "thoigianbatdau");
    $sothang = filter_input(INPUT_POST, "sothang");
    $songaybaoduong = filter_input(INPUT_POST, "songaybaoduong");
    $gia = $smarty->clean(filter_input(INPUT_POST, "gia"), true);
    $soluong = filter_input(INPUT_POST, "soluongTV");
    $_hieuluc = filter_input(INPUT_POST, "hieuluc");
    $chuTK = filter_input(INPUT_POST, "chuTK");
    $soTK = filter_input(INPUT_POST, "soTK");
    $tennganhang = filter_input(INPUT_POST, "tennganhang");

    $hieuluc = isset($_hieuluc) ? 1 : 0;

    $query = "
        SELECT 1 
        FROM `hopdong` 
        WHERE `MaHD`='$maHD'
        ;
    ";
    $result = mysql_query($query);
    if (mysql_num_rows($result) != 0) {
        $success = -1;
    } else {
        $query_hopdong = "
            INSERT INTO `hopdong`(`MaHD`,`MaDD`,`MaPTTT`,`ThoiGianBatDau`,`SoThang`,`SoNgayBaoDuong`,`Gia`,`SoLuong`,`HieuLuc`,`GhiChu`,`ChuTK`,`SoTK`,`TenNH`)
            VALUES(
                '$maHD',
                $maDD,
                $maPTTT,
                '$thoigianbatdau',
                $sothang,
                $songaybaoduong,
                $gia,
                $soluong,
                $hieuluc,
                '',
                '$chuTK',
                '$soTK',
                '$tennganhang'
            );
        ";
        $result_hopdong = mysql_query($query_hopdong);
        if ($result_hopdong) {
            $success = 1;
        } else {
            $success = 0;
        }
    }
    $smarty->assign("success", $success);
}

// Unprocessing
if ($action == "view") {
    $query = "
        SELECT `diadiem`.`MaDD`, `diadiem`.`TenDD`, `DiaChi`, `phuongxa`.`TenPX`, `quanhuyen`.`TenQH`, `thanhpho`.`TenTP`, COUNT(`hopdong`.`MaHD`) AS SoHopDong
        FROM (`diadiem`, `diachi`, `phuongxa`, `quanhuyen`, `thanhpho`)
        LEFT JOIN `hopdong` ON (`hopdong`.`MaDD` = `diadiem`.`MaDD`)
        WHERE `diadiem`.`MaDC` = `diachi`.`MaDC`   
            AND `diachi`.`MaPX` = `phuongxa`.`MaPX` 
            AND `diachi`.`MaQH` = `quanhuyen`.`MaQH`
            AND `diachi`.`MaTP` = `thanhpho`.`MaTP`
        GROUP BY `diadiem`.`MaDD`, `TenDD`, `DiaChi`, `phuongxa`.`TenPX`, `quanhuyen`.`TenQH`, `thanhpho`.`TenTP`
        ORDER BY `thanhpho`.`TenTP`, `quanhuyen`.`TenQH`, `phuongxa`.`TenPX`, `DiaChi`, `TenDD`        
        ;
    ";
    $result = mysql_query($query);
    while ($row = mysql_fetch_array($result)) {
        $address[] = $row;
    }
    $smarty->assign("address", $address);

    if ($content == "lienhe") {
        $id = (int) $smarty->clean(filter_input(INPUT_GET, "id"), true);
        $query = "
            SELECT `diadiem`.`TenDD`, `TenLH`, `SDT`, `TenCV`
            FROM `lienhe_chucvu`, `lienhe`, `chucvu`, `diadiem`
            WHERE `lienhe_chucvu`.`MaLH` = `lienhe`.`MaLH`
                AND `lienhe_chucvu`.`MaCV` = `chucvu`.`MaCV`
                AND (`diadiem`.`MaLH1` = `lienhe_chucvu`.`MaLHCV` OR `diadiem`.`MaLH2` = `lienhe_chucvu`.`MaLHCV`) 
                AND `diadiem`.`MaDD` = $id
            ;
            ";
        $result = mysql_query($query);
        while ($row = mysql_fetch_array($result)) {
            $diadiem_lienhe[] = $row;
        }
        $smarty->assign("diadiem_lienhe", $diadiem_lienhe);
        $smarty->display("diadiem_lienhe.tpl");
        die();
    }

    if ($content == "hopdong") {
        $id = (int) $smarty->clean(filter_input(INPUT_GET, "id"), true);

        $str_id = "";
        if ($id > 0) {
            $str_id = "AND `hopdong`.`MaDD` = $id";
        }
        $query = "
            SELECT `diadiem`.`MaDD`, `diadiem`.`TenDD`, `MaHD`, `SoThang`,`Gia`, `SoLuong`, `SoNgayBaoDuong`, 
                `ThoiGianBatDau`, (`ThoiGianBatDau` + INTERVAL `SoThang` MONTH + INTERVAL `SoNgayBaoDuong` DAY) AS `ThoiGianKetThuc`, 
                `TenPTTT`, FLOOR(`SoThang` / `hopdong`.`MaPTTT`) AS SoDotThanhToan, `dotthanhtoan`.`MaDTT`, `TenDTT`, `HieuLuc`, `GhiChu`
            FROM `diadiem`, `hopdong`, `phuongthucthanhtoan`, `dotthanhtoan`
            WHERE `hopdong`.`MaDD` = `diadiem`.`MaDD`
                AND `hopdong`.`MaPTTT` = `phuongthucthanhtoan`.`MaPTTT`
                AND  (DAY(`ThoiGianBatDau`) BETWEEN `dotthanhtoan`.`NgayBatDau` AND `dotthanhtoan`.`NgayKetThuc`)
                $str_id
                ;
            ";
        $result = mysql_query($query);
        while ($row = mysql_fetch_array($result)) {
            $diadiem_hopdong[] = $row;
        }
        $smarty->assign("diadiem_hopdong", $diadiem_hopdong);
        $smarty->assign("id", $id);
        $smarty->display("diadiem_hopdong.tpl");
        die();
    }
}

// OK
if ($action == "edit") {
    $found = 0;
    $_id = filter_input(INPUT_GET, "id");
    $id = $smarty->clean($_id, false);
    if ($id > 0) {
        $query = "
            SELECT *
            FROM `hopdong`
            WHERE `ID` = $id
            ;
        ";
        $result = mysql_query($query);
        if (mysql_num_rows($result) == 1) {
            $found = 1;
            while ($row = mysql_fetch_array($result)) {
                $hopdong[] = $row;
            }
            $smarty->assign("hopdong", $hopdong);
        } else {
            header("Location: /hopdong");
        }
    } else {
        //$action = null;
        header("Location: /hopdong");
    }
}

// OK
if ($submit == "update") {
    $success = 0;
    $maDD = filter_input(INPUT_POST, "maDD");
    $maHD = addslashes(filter_input(INPUT_POST, "maHD"));
    $phuongthucthanhtoan = filter_input(INPUT_POST, "phuongthucthanhtoan");
    $thoigianbatdau = filter_input(INPUT_POST, "thoigianbatdau");
    $sothang = filter_input(INPUT_POST, "sothang");
    $songaybaoduong = filter_input(INPUT_POST, "songaybaoduong");
    $gia = $smarty->clean(filter_input(INPUT_POST, "gia"));
    $soluongTV = filter_input(INPUT_POST, "soluongTV");
    $hieuluc = filter_input(INPUT_POST, "hieuluc");
    $hieuluc = isset($hieuluc) ? 1 : 0;
    $chuTK = filter_input(INPUT_POST, "chuTK");
    $soTK = filter_input(INPUT_POST, "soTK");
    $tennganhang = filter_input(INPUT_POST, "tennganhang");
    $ID = filter_input(INPUT_POST, "ID");

    $query = "
        SELECT 1
        FROM `hopdong`
        WHERE `MaHD` = '$maHD'AND
            `ID` != $ID
        ;
    ";
    $result = mysql_query($query);

    if (mysql_num_rows($result) != 0) {
        $success = -1;
    } else {
        $query_hopdong = "
            UPDATE `hopdong`
            SET `MaHD` = '$maHD',
              `MaDD` = $maDD,
              `MaPTTT` = $phuongthucthanhtoan,
              `ThoiGianBatDau` = '$thoigianbatdau',
              `SoThang` = $sothang,
              `SoNgayBaoDuong` = $songaybaoduong,
              `Gia` = $gia,
              `SoLuong` = $soluongTV,
              `HieuLuc` = $hieuluc,
              `GhiChu` = '',
              `ChuTK` = '$chuTK',
              `SoTK` = '$soTK',
              `TenNH` = '$tennganhang'
            WHERE `ID` = $ID
            ;
        ";
        $result_hopdong = mysql_query($query_hopdong);

        if ($result_hopdong) {
            $success = 1;
        } else {
            $success = 0;
        }
    }
    $smarty->assign("success", $success);
}

// Unprocessing
if ($action == "delete") {
    $success = 0;

    $maDD = filter_input(INPUT_GET, "id");

    $query = "
        SELECT *
        FROM `diadiem`
        WHERE `MaDD`=$maDD
        ;
    ";
    $result = mysql_query($query);

    if (mysql_num_rows($result) == 0) {
        $success = -1;
    } else {
        $query = "
            DELETE FROM `diadiem`
            WHERE `MaDD` = $maDD
            ;
        ";
        $result = mysql_query($query);
        if ($result) {
            $success = 1;
        } else {
            $success = 0;
        }
    }
    $smarty->assign("success", $success);
}

//OK
if ($action == "expiring") {
    if ($submit == "xem") {
        $cbbExpiring = (int) strtolower(filter_input(INPUT_POST, "cbbExpiring"));
        $hieuluc = (int) strtolower(filter_input(INPUT_POST, "hieuluc"));
        $query = "
            SELECT `TenTP`, `diadiem`.`TenDD`, `diadiem`.`MaDD`, `diadiem`.`TenDD`, `MaHD`, `SoThang`,`Gia`, `SoLuong`, `SoNgayBaoDuong`, 
                `ThoiGianBatDau`, (`ThoiGianBatDau` + INTERVAL `SoThang` MONTH + INTERVAL `SoNgayBaoDuong` DAY) AS `ThoiGianKetThuc`, 
                `TenPTTT`, FLOOR(`SoThang` / `hopdong`.`MaPTTT`) AS SoDotThanhToan, `dotthanhtoan`.`MaDTT`, `TenDTT`, `HieuLuc`, `GhiChu`,
                DATEDIFF(`ThoiGianBatDau` + INTERVAL `SoThang` MONTH + INTERVAL `SoNgayBaoDuong` DAY,DATE(NOW())) AS `RemainingExpiring`
            FROM `diadiem`, `hopdong`, `phuongthucthanhtoan`, `dotthanhtoan`, `diachi`, `thanhpho`
            WHERE `hopdong`.`MaDD` = `diadiem`.`MaDD`
                AND `diachi`.`MaDC` = `diadiem`.`MaDC`
		AND `thanhpho`.`MaTP` = `diachi`.`MaTP`
                AND `hopdong`.`MaPTTT` = `phuongthucthanhtoan`.`MaPTTT`
                AND  (DAY(`ThoiGianBatDau`) BETWEEN `dotthanhtoan`.`NgayBatDau` AND `dotthanhtoan`.`NgayKetThuc`)
                AND DATEDIFF(`ThoiGianBatDau` + INTERVAL `SoThang` MONTH + INTERVAL `SoNgayBaoDuong` DAY,DATE(NOW())) <= 30*$cbbExpiring
        ";
        if ($hieuluc != -1) {
            $query.= "AND `HieuLuc`=$hieuluc;";
        }
        $result = mysql_query($query);
        while ($row = mysql_fetch_array($result)) {
            $hopdong[] = $row;
        }
        if (isset($hopdong)) {
            $smarty->assign("hopdong", $hopdong);
        }
        $smarty->assign("cbbExpiring", $cbbExpiring);
        $smarty->assign("hieuluc", $hieuluc);
    }

    if ($submit == "send mail") {
        // Load email
        $dataEmail = filter_input(INPUT_POST, "dataEmail");

        $query = "select * from email";
        $result = mysql_query($query);
        if (mysql_num_rows($result) > 0) {
            while ($row = mysql_fetch_array($result)) {
                $email[] = $row;
            }
        }
        $mail = new PHPMailer_Target();

        foreach ($email as $e) {
            $mail->addAddress($e['Email']);
        }
        $mail->Subject = "[Cafe LCD] Expiring Contract";

        $mail->Body = $dataEmail;

        if (!$mail->Send()) {
            $error = $mail->ErrorInfo;
            $smarty->assign("error", $error);
        } else {
            $error = "0";
            $smarty->assign("error", $error);
        }
    }
}

$smarty->assign("action", $action);

$smarty->display("hopdong.tpl");
